import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

my_now_course_router = APIRouter(prefix="/school/nowCourse", tags=["本周课程表&我的课表"],
                                 dependencies=[Depends(oauth2_scheme)])


class WeeklyCourseScheduleSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    fclassno: Optional[str] = Field("0", description="班级编号，默认0")
    fbegdate: Optional[str] = Field("2000-01-01", description="课程开始日期，默认2000-01-01")
    fenddate: Optional[str] = Field("2000-01-01", description="课程结束日期，默认2000-01-01")
    fyear: Optional[str] = Field("2024", description="课程年份，默认2024")
    teacherno: Optional[str] = Field("", description="教师编号")
    studentno: Optional[str] = Field("", description="学生编号")


@my_now_course_router.get("/list", summary="本周课程表&我的课表查询接口")
def list_my_now_courses(search: WeeklyCourseScheduleSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from a_tc_course b,a_ts_class c,a_ts_course d,a_ts_teacher e"
    sql = sql + " where 1=1"
    sql = sql + " and b.fclass_no=c.fclass_no"
    sql = sql + " and b.fcourse_no=d.fcourse_no"
    sql = sql + " and b.fteacher_no=e.fteacher_no"
    if search.fsiteNo != "":
        sql = sql + " and a.fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fyear != "":
        sql = sql + " and a.fyear = :fyear "
        param["fyear"] = search.fyear
    if search.fclassno != "":
        sql = sql + " and b.fclass_no = :fclassno "
        param["fclassno"] = search.fclassno
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select b.id,b.fclassdate,b.fweek,b.ftime,b.fsite_no,b.fpoint_no,b.fclass_no,b.fcourse_no,"
    sql = sql + " b.fplan_begtime,b.fplan_endtime,b.fteacher_no,b.freal_begtime,"
    sql = sql + " b.freal_endtime,b.fattend_no,b.fnote,b.fstate,b.fupddate,b.foperator"
    sql = sql + " c.fclass_name,d.fcourse_name,e.fteacher_name"
    sql = sql + " from a_tc_course b,a_ts_class c,a_ts_course d,a_ts_teacher e"
    sql = sql + " where 1=1"
    sql = sql + " and b.fclass_no=c.fclass_no"
    sql = sql + " and b.fcourse_no=d.fcourse_no"
    sql = sql + " and b.fteacher_no=e.fteacher_no"
    if search.fsiteNo != "":
        sql = sql + " and a.fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fyear != "":
        sql = sql + " and a.fyear = :fyear "
        param["fyear"] = search.fyear
    if search.fclassno != "":
        sql = sql + " and b.fclass_no = :fclassno "
        param["fclassno"] = search.fclassno
    if search.order == "desc":
        sql = sql + " order by  b.id desc "
    else:
        sql = sql + " order by  b.id asc "
    sql = sql + " limit :limit offset :offset "
    param["offset"] = search.offset
    param["limit"] = search.limit
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['id'] = data[key][0]
        d1['fclassdate'] = data[key][1]
        d1['fweek'] = data[key][2]
        d1['ftime'] = data[key][3]
        d1['fsiteno'] = data[key][4]
        d1['fpointno'] = data[key][5]
        d1['fclassno'] = data[key][6]
        d1['fcourseno'] = data[key][7]
        d1['fplanbegtime'] = data[key][8]
        d1['fplanendtime'] = data[key][9]
        d1['fteacherno'] = data[key][10]
        d1['frealbegtime'] = data[key][11]
        d1['frealendtime'] = data[key][12]
        d1['fattendno'] = data[key][13]
        d1['fnote'] = data[key][14]
        d1['fstate'] = data[key][15]
        d1['fupddate'] = data[key][16]
        d1['foperator'] = data[key][17]
        d1['fclassname'] = data[key][18]
        d1['fcoursename'] = data[key][19]
        d1['fteachername'] = data[key][20]
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)
